package cn.green.core.excel.model.impls;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import cn.green.core.excel.model.Column;
import cn.green.core.model.QField;
import lombok.Getter;
import lombok.Setter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author wang.yishu
 * @version 1.0
 * @className SelectorColumn
 * @description 下拉单元格
 * @date 2025/1/6 10:21
 */
@Setter
@Getter
public class SelectorColumn extends Column {
    /**
     * 下拉单元格的下拉选项
     */
    protected JSONArray options;

    public static final String SPLIT_SYMBOL = "_code_";

    public SelectorColumn(Integer index, QField field) {
        super(index, field);
    }

    @Override
    public List<HSSFCell> generateXlsTitle(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row) {
        HSSFCell cell = row.createCell(this.index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        if (this.xlsTitleCellStyle != null) {
            cell.setCellStyle(this.xlsTitleCellStyle);
        }
        cell.setCellValue(this.columnName);
        if (this.options != null) {
            String[] selectOptions = new String[this.options.size()];
            for (int i = 0; i < this.options.size(); i++) {
                selectOptions[i] = this.options.getJSONObject(i).getString("label") + SPLIT_SYMBOL + this.options.getJSONObject(i).getString("value");
            }
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(selectOptions);
            HSSFDataValidation data = new HSSFDataValidation(new CellRangeAddressList(1, this.maxRow, this.index, this.index), constraint);
            data.setSuppressDropDownArrow(false);
            try {
                //510101 单位管理费用_行政管理费用_30201 办公费
                sheet.addValidationData(data);
            } catch (Exception e) {
                //当单元格数据大于255了，执行另外的工具
                HSSFDataValidation dataValidationList255Col = getDataValidationList255Col(sheet, workbook, 1, this.maxRow, index, index, Arrays.asList(selectOptions), index);
                sheet.addValidationData(dataValidationList255Col);
            }
        }
        return Collections.singletonList(cell);
    }

    @Override
    public List<XSSFCell> generateXlsxTitle(XSSFWorkbook workbook, XSSFSheet sheet, XSSFRow row) {
        XSSFCell cell = row.createCell(this.index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        if (this.xlsxTitleCellStyle != null) {
            cell.setCellStyle(this.xlsxTitleCellStyle);
        }
        cell.setCellValue(this.columnName);
        if (this.options != null) {
            String[] selectOptions = new String[this.options.size()];
            for (int i = 0; i < this.options.size(); i++) {
                selectOptions[i] = this.options.getJSONObject(i).getString("label") + SPLIT_SYMBOL + this.options.getJSONObject(i).getString("value");
            }
            DataValidationHelper helper = sheet.getDataValidationHelper();
            //设置起始行为1（从0行开始）、终止行为xls的最大值
            CellRangeAddressList addressList = new CellRangeAddressList(1, this.maxRow, index, index);
            //设置下拉框数据
            DataValidationConstraint constraint = helper.createExplicitListConstraint(selectOptions);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            dataValidation.setSuppressDropDownArrow(false);
            try {
                sheet.addValidationData(dataValidation);
            } catch (IllegalArgumentException ignore) {
            }
        }
        return Collections.singletonList(cell);
    }

    @Override
    public List<HSSFCell> generateXlsCell(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row, Object cellVal) {
        HSSFCell cell = row.createCell(this.index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        if (cellVal != null && !"".equals(cellVal) && this.options != null && !this.options.isEmpty()) {
            List<Object> target = options.stream().filter(item -> ((JSONObject) item).getString("value").equals(cellVal.toString())).collect(Collectors.toList());
            if (!target.isEmpty()) {
                String cellValStr = ((JSONObject) target.get(0)).getString("label") + SPLIT_SYMBOL + ((JSONObject) target.get(0)).getString("value");
                cell.setCellValue(cellValStr);
            }
        } else {
            cell.setCellValue("");
        }
        return Collections.singletonList(cell);
    }

    @Override
    public List<XSSFCell> generateXlsxCell(XSSFWorkbook workbook, XSSFSheet sheet, XSSFRow row, Object cellVal) {
        XSSFCell cell = row.createCell(this.index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        if (cellVal != null && !"".equals(cellVal) && this.options != null && !this.options.isEmpty()) {
            List<Object> target = options.stream().filter(item -> ((JSONObject) item).getString("value").equals(cellVal.toString())).collect(Collectors.toList());
            if (!target.isEmpty()) {
                String cellValStr = ((JSONObject) target.get(0)).getString("label") + SPLIT_SYMBOL + ((JSONObject) target.get(0)).getString("value");
                cell.setCellValue(cellValStr);
            }
        }
        return Collections.singletonList(cell);
    }

    @Override
    public Map<String, Object> getValueFromXlsCell(Map<String, HSSFCell> cells) {
        Map<String, Object> data = new HashMap<>();
        cells.forEach((fieldName, cell) -> {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                String cellValue = cell.getStringCellValue();
                if (cellValue != null && !cellValue.isEmpty()) {
                    if (cellValue.contains(SPLIT_SYMBOL)) {
                        data.put(fieldName, cellValue.split(SPLIT_SYMBOL)[1]);
                    } else {
                        data.put(fieldName, cellValue);
                    }
                }
            }
        });
        return data;
    }

    @Override
    public Map<String, Object> getValueFromXlsxCell(Map<String, XSSFCell> cells) {
        Map<String, Object> data = new HashMap<>();
        cells.forEach((fieldName, cell) -> {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                String cellValue = cell.getStringCellValue();
                if (cellValue != null && !cellValue.isEmpty()) {
                    if (cellValue.contains(SPLIT_SYMBOL)) {
                        data.put(fieldName, cellValue.split(SPLIT_SYMBOL)[1]);
                    } else {
                        data.put(fieldName, cellValue);
                    }
                }
            }
        });
        return data;
    }


    /**
     * 解决下拉菜单字符过多问题
     * @param sheet
     * @param wbCreat
     * @param firstRow
     * @param firstCol
     * @param endRow
     * @param endCol
     * @param colName
     * @return
     */
    private HSSFDataValidation getDataValidationList255Col(HSSFSheet sheet, HSSFWorkbook wbCreat,
                                                          int firstRow, int endRow,
                                                          int firstCol, int endCol,
                                                          List<String> colName, int index)
    {
        String[] dataArray = colName.toArray(new String[0]);
        HSSFSheet hidden = wbCreat.createSheet("hidden_" + index);
        HSSFCell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++)
        {
            String name = dataArray[i];
            HSSFRow row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = wbCreat.createName();
        namedCell.setNameName("hidden_" + index);
        namedCell.setRefersToFormula("hidden_" + index + "!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden_" + index);

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,endCol);

        //将第二个sheet设置为隐藏
        //  wbCreat.setSheetHidden(1, true);
        // sheet.addValidationData(validation);
        return new HSSFDataValidation(addressList, constraint);
    }

}
